{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Big Query Machine Learning (BQML)\n",
    "\n",
    "**Learning Objectives**\n",
    "- Understand that it is possible to build ML models in Big Query\n",
    "- Understand when this is appropriate\n",
    "- Experience building a model using BQML\n",
    "\n",
    "# Introduction\n",
    "\n",
    "BigQuery is more than just a data warehouse, it also has some ML capabilities baked into it. \n",
    "\n",
    "As of January 2019 it is limited to linear models, but what it gives up in complexity, it gains in ease of use.\n",
    "\n",
    "BQML is a great option when a linear model will suffice, or when you want a quick benchmark to beat, but for more complex models such as neural networks you will need to pull the data out of BigQuery and into an ML Framework like TensorFlow.\n",
    "\n",
    "In this notebook, we will build a naive model using BQML. **This notebook is intended to inspire usage of BQML, we will not focus on model performance.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set up environment variables and load necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip freeze | grep google-cloud-bigquery==1.21.0 || pip install google-cloud-bigquery==1.21.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After installation, restart the kernel by selecting **Kernel** > **Restart Kernel**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT = \"cloud-training-demos\"  # Replace with your PROJECT\n",
    "REGION = \"us-central1\"            # Choose an available region for Cloud MLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ[\"PROJECT\"] = PROJECT\n",
    "os.environ[\"REGION\"] = REGION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext google.cloud.bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create BigQuery dataset\n",
    "\n",
    "Prior to now we've just been reading an existing BigQuery table, now we're going to create our own so so we need some place to put it. In BigQuery parlance, `Dataset` means a folder for tables. \n",
    "\n",
    "We will take advantage of BigQuery's [Python Client](https://cloud.google.com/bigquery/docs/reference/libraries#client-libraries-install-python) to create the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "bq = bigquery.Client(project = PROJECT)\n",
    "\n",
    "dataset = bigquery.Dataset(bq.dataset(\"bqml_taxifare\"))\n",
    "try:\n",
    "    bq.create_dataset(dataset) # will fail if dataset already exists\n",
    "    print(\"Dataset created\")\n",
    "except:\n",
    "    print(\"Dataset already exists\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create model\n",
    "\n",
    "To create a model ([Documentation](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create))\n",
    "1. Use `CREATE MODEL` and provide a destination table for resulting model. Alternatively we can use `CREATE OR REPLACE MODEL` which allows overwriting an existing model.\n",
    "2. Use `OPTIONS` to specify the model type (linear_reg or logistic_reg). There are many more options [we could specify](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create#model_option_list), such as regularization and learning rate, but we'll accept the defaults.\n",
    "3. Provide the query which fetches the training data \n",
    "\n",
    "#### **Exercise 1**\n",
    "Use the query we created in the previous lab to Clean the Data to now train a Linear Regression model with BQML called `taxifare_model`. This should amount to adding a line to create the model and adding OPTIONS to specify the model type. Our label will be sum of `tolls_amount` and `fare_amount` and for features will use the pickup datetime and pickup & dropoff latitude and longitude.\n",
    "\n",
    "HINT: Have a look at [Step Two of this tutorial](https://cloud.google.com/bigquery/docs/bigqueryml-natality) if you get stuck or if you want to see another example.\n",
    "\n",
    "**Your query could take about two minutes to complete.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get training statistics\n",
    "\n",
    "Because the query uses a `CREATE MODEL` statement to create a table, you do not see query results. The output is an empty string.\n",
    "\n",
    "To get the training results we use the [`ML.TRAINING_INFO`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-train) function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Exercise 2**\n",
    "\n",
    "After completing the exercise above, query the training information of the model you created. Have a look at [Step Three and Four of this tutorial](https://cloud.google.com/bigquery/docs/bigqueryml-natality) to see a similar example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'eval_loss' is reported as mean squared error. Your RMSE should be about **8.29**. Your results may vary."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Predict\n",
    "\n",
    "To use our model to make predictions, we use `ML.PREDICT`\n",
    "\n",
    "#### **Exercise 3**\n",
    "\n",
    "Lastly, use the `taxifare_model` you trained above to infer the cost of a taxi ride that occurs at 10:00 am on January 3rd, 2014 going\n",
    "from the Google Office in New York (latitude: 40.7434, longitude: -74.0080) to the JFK airport (latitude: 40.6413, longitude: -73.7781)\n",
    "\n",
    "**Hint**: Have a look at [Step Five of this tutorial](https://cloud.google.com/bigquery/docs/bigqueryml-natality) if you get stuck or if you want to see another example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT\n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Recap\n",
    "\n",
    "The value of BQML is its ease of use:\n",
    "\n",
    "- We created a model with just two additional lines of SQL\n",
    "- We never had to move our data out of BigQuery\n",
    "- We didn't need to use an ML Framework or code, just SQL\n",
    "\n",
    "There's lots of work going on behind the scenes make this look easy. For example BQML is automatically creating a training/evaluation split, tuning our learning rate,  and one-hot encoding features if neccesary. When we move to TensorFlow these are all things we'll need to do ourselves. \n",
    "\n",
    "This notebook was just to inspire usagage of BQML, the current model is actually very poor. We'll prove this in the next lesson by beating it with a simple heuristic. \n",
    "\n",
    "We could improve our model considerably with some feature engineering but we'll save that for a future lesson. Also there are additional BQML functions such as `ML.WEIGHTS` and `ML.EVALUATE` that we haven't even explored. If you're interested in learning more about BQML I encourage you to [read the offical docs](https://cloud.google.com/bigquery/docs/bigqueryml).\n",
    "\n",
    "From here on out we'll focus on pulling data out of BigQuery and building models using TensorFlow, which is more effort but also offers much more flexibility."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright 2022 Google Inc.\n",
    "Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at\n",
    "http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
